package de.stalinski.accounting;

import de.stalinski.logging.FrbrLogger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import javax.servlet.http.HttpServlet;

public class PricesTableConnector extends HttpServlet {

    private FrbrLogger logger = FrbrLogger.instance();
    private static String sUsr = "root";
    private static String sPwd = "admin";
    private static String sDB = "frbrUsers";
    private static String sTable = "prices_drinks";
    private static Connection connection;
    private static String sHost = "localhost";
    private static String sConn = "jdbc:mysql://" + sHost + ":3306/" + sDB;
    private ArrayList<String> drinks = new ArrayList();

    public PricesTableConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        try {
            if (connection == null) {
                connection = DriverManager.getConnection(sConn, sUsr, sPwd);
            }

        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
    }

    public Double getPriceFor(String sort) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        if (connection.isClosed()) {
            connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        }

        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select SORT,VALUE from " + sTable);
            while (rs.next()) {
                String value = rs.getString("SORT");
                if (value.equals(sort)) {
                    return Double.parseDouble(rs.getString("VALUE"));
                }
            }
            Collections.sort(drinks);
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        connection.close();
        return 0.0;
    }

    public ArrayList<String> getSorts() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        if (connection.isClosed()) {
            connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        }

        drinks.clear();
        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select * from " + sTable);
            while (rs.next()) {
                String value = rs.getString("SORT");
                drinks.add(value);
            }
            Collections.sort(drinks);
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        connection.close();
        return drinks;
    }

    public void updateStock(String sort, int amount) throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        rs = st.executeQuery("select * from " + sTable);

        st.execute("UPDATE " + sTable + " SET STOCK=STOCK-" + amount + " WHERE SORT ='" + sort + "'");
        connection.close();
    }

    public String getPrices(String fn, String sn, String un, String id) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(sConn, sUsr, sPwd);
        drinks.clear();
        String out = "<table class=\"status\"><tbody><tr>";
        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select * from " + sTable);

            int i = 0;

            while (rs.next()) {
                String value = rs.getString("SORT");
                drinks.add(value);
            }
            Collections.sort(drinks);

            for (String s : drinks) {

                String link = "amount.jsp?fn=" + fn + "&sn=" + sn + "&un=" + un + "&id=" + id + "&sort=" + s;


                if (i > 3) {
                    out += "<tr>";
                }
                out += "<td>" +
                        "<a href=\"" + link + "\">" +
                        s +
                        "</a>" +
                        "</td>";

                if (i > 3) {

                    i = 0;
                }
                i++;
            }
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        connection.close();
        return out += "</tr></tbody></table>";
    }
}

